package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class GetData {
	private static final String S1 = " SELECT TOP 1000 "
			+ "            j.punch_type AS punchType, "
			+ "            CASE WHEN j.fold_type = 6 THEN jfc.value ELSE j.fold_count END AS foldCount, "
			+ "            j.bind_count AS bindCount, "
			+ "            null AS outputPaperType, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.in_related_job_id,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.out_related_job_id,'')  ELSE jdf.out_related_job_id END  AS relatedJobID, "
			+ "            j.fax_sender_number AS faxSenderNumber, "
			+ "            jdf.fax_out_total_pages AS faxTotalPages, "
			+ "            j.mailbox_document_id AS mailboxDocumentID, "
			+ "            j.mailbox_number AS mailboxNumber, "
			+ "            j.staple_count AS stapleCount, "
			+ "            j.fax_address_book_name AS faxAddressBookName, "
			+ "            j.copies AS copies, "
			+ "            j.punch_count AS punchCount, "
			+ "            j.destination_address AS destinationAddress, "
			+ "            null AS nUp, "
			+ "            j.fault_reason AS jobFaultReason, "
			+ "            j.user_name AS userName, "
			+ "            d.DeviceModelName AS modelName, "
			+ "            j.destination AS destination, "
			+ "            j.host_address AS ipAddress, "
			+ "            null AS inputImpressions, "
			+ "            null AS inputTray, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.in_pages,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.out_pages,'')  ELSE jdf.out_pages END  AS faxPages, "
			+ "            j.account_id AS accountID, "
			+ "            j.start_time AS startTime, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_in,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_out,'')  ELSE j.f_code_out END  AS fCode, "
			+ "            j.collection_time AS collectionTime, "
			+ "            null AS outputTray, "
			+ "            CASE WHEN j.fold_type = 6 THEN 1 ELSE 0 END AS foldCounterFlg, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_duration,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_duration,0)  ELSE j.fax_out_duration END  AS faxDuration, "
			+ "            d.DeviceDisplayName AS printerName, "
			+ "            null AS stampType, "
			+ "            null AS sheets, "
			+ "            j.job_status AS jobStatus, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_name,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_name,'')  ELSE j.fax_out_remote_name END  AS faxRemoteName, "
			+ "            (case when ISNULL(j.job_detail_type,0) NOT IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_in_line = 1 or j.fax_in_line = 2 or j.fax_in_line = 3) then convert(nvarchar, j.fax_in_channel) + '(' + (case when j.fax_in_line = 1 then N'内線' when j.fax_in_line = 2 then N'外線' when j.fax_in_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_in_channel is not null then (convert(nvarchar, j.fax_in_channel)) else N'不明' end ) when ISNULL(j.job_detail_type,0) IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_out_line = 1 or j.fax_out_line = 2 or j.fax_out_line = 3) then convert(nvarchar, j.fax_out_channel) + '(' + (case when j.fax_out_line = 1 then N'内線' when j.fax_out_line = 2 then N'外線' when j.fax_out_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_out_channel is not null then (convert(nvarchar, j.fax_out_channel)) else N'不明' end ) else N'不明' end ) AS faxLineChannel, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_indication_type,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_indication_type,0)  ELSE j.fax_out_remote_indication_type END  AS faxRemoteIndicationType, "
			+ "            CASE WHEN j.fold_type = 6 THEN jfc.type ELSE j.fold_type END AS foldType, "
			+ "            null AS rollSize, "
			+ "            j.fax_out_status AS faxOutStatus, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.in_transmission_rate,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.out_transmission_rate,'')  ELSE jdf.out_transmission_rate END  AS faxTransmissionRate, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_start_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_start_time  ELSE j.fax_out_start_time END  AS faxStartTime, "
			+ "            null AS rollLength, "
			+ "            j.fax_speed_number AS faxShortDialInNumber, "
			+ "            j.fax_dial_in_number AS faxDialInNumber, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_phone_number,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_phone_number,'')  ELSE j.fax_out_phone_number END  AS faxPhoneNumber, "
			+ "            j.job_type AS jobType, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (317,605,606,614,615,701,703,704,705,706,622,707,315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612,301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.protocol,0) WHEN ISNULL(job_detail_type,0) IN (317,605,606,614,615,701,703,704,705,706,622,707) THEN ISNULL(j.destination_protocol,0) WHEN ISNULL(job_detail_type,0) IN (315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612) THEN ISNULL(j.fax_in_protocol,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_protocol,0)  END  AS protocol, "
			+ "            j.host_name AS hostName, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_complete_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_complete_time  ELSE j.fax_out_complete_time END  AS faxCompleteTime, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_result,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_result,0)  ELSE j.fax_out_result END  AS faxResult, "
			+ "            j.jobID AS jobID, "
			+ "            j.fault_code AS jobFaultCode, "
			+ "            j.serialNumber AS serialNumber, "
			+ "            null AS inputColorMode, "
			+ "            j.fax_resend AS faxReconnectFlag, "
			+ "            j.complete_time AS completeTime, "
			+ "            j.card_id AS cardID, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_id,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_id,'')  ELSE j.fax_out_remote_id END  AS faxRemoteID, "
			+ "            j.job_client_id AS jobClientID, "
			+ "            j.uuid AS uuid, "
			+ "            j.document_name AS documentName, "
			+ "            null AS outputColorMode, "
			+ "            null AS inputPaperSize, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.in_size,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(jdf.out_size,'')  ELSE jdf.out_size END  AS faxSize, "
			+ "            null AS impressions, "
			+ "            j.modelCode AS modelCode, "
			+ "            null AS outputPaperSize, "
			+ "            CASE WHEN ISNULL(j.document_format,0) <> 0 THEN j.document_format WHEN ISNULL(j.destination_document_format,0) <> 0 THEN j.destination_document_format  ELSE j.destination_document_format END  AS documentFormat, "
			+ "            CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_call,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_call,0)  ELSE j.fax_out_call END  AS faxCall, "
			+ "            j.job_template AS jobTemplate, "
			+ "            j.job_detail_type AS jobDetailType, "
			+ "            j.staple_type AS stapleType, "
			+ "            null AS inputPaperType, "
			+ "            j.user_id AS jobOwnerName "
			+ "        FROM "
			+ "            jobLogInfo j LEFT JOIN jobLogFoldCounterInfo jfc ON j.autoNumber = jfc.jobLogID, "
			+ "            jobLogDetailFaxInfo jdf, "
			+ "            DMDeviceTempInfo d "
			+ "        WHERE "
			+ "            (j.autoNumber = jdf.jobLogID) AND "
			+ "            (j.modelCode = d.DeviceModelCode) AND "
			+ "            (j.serialNumber = d.DeviceSerialNumber) AND "
			+ "            ( (1=1)  AND  (j.complete_time < '2013/7/20 0:0') ) ";

	private static final String S2 = " SELECT TOP 1000 "
			+ "    j.punch_type AS punchType, "
			+ "    CASE WHEN j.fold_type = 6 THEN jfc.value ELSE j.fold_count END AS foldCount, "
			+ "    j.bind_count AS bindCount, "
			+ "    null AS outputPaperType, "
			+ "    null AS relatedJobID, "
			+ "    j.fax_sender_number AS faxSenderNumber, "
			+ "    null AS faxTotalPages, "
			+ "    j.mailbox_document_id AS mailboxDocumentID, "
			+ "    j.mailbox_number AS mailboxNumber, "
			+ "    j.staple_count AS stapleCount, "
			+ "    j.fax_address_book_name AS faxAddressBookName, "
			+ "    j.copies AS copies, "
			+ "    j.punch_count AS punchCount, "
			+ "    j.destination_address AS destinationAddress, "
			+ "    jdi.n_up AS nUp, "
			+ "    j.fault_reason AS jobFaultReason, "
			+ "    j.user_name AS userName, "
			+ "    d.DeviceModelName AS modelName, "
			+ "    j.destination AS destination, "
			+ "    j.host_address AS ipAddress, "
			+ "    jdi.impressions AS inputImpressions, "
			+ "    null AS inputTray, "
			+ "    null AS faxPages, "
			+ "    j.account_id AS accountID, "
			+ "    j.start_time AS startTime, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_in,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_out,'')  ELSE j.f_code_out END  AS fCode, "
			+ "    j.collection_time AS collectionTime, "
			+ "    null AS outputTray, "
			+ "    CASE WHEN j.fold_type = 6 THEN 1 ELSE 0 END AS foldCounterFlg, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_duration,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_duration,0)  ELSE j.fax_out_duration END  AS faxDuration, "
			+ "    d.DeviceDisplayName AS printerName, "
			+ "    null AS stampType, "
			+ "    null AS sheets, "
			+ "    j.job_status AS jobStatus, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_name,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_name,'')  ELSE j.fax_out_remote_name END  AS faxRemoteName, "
			+ "    (case when ISNULL(j.job_detail_type,0) NOT IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_in_line = 1 or j.fax_in_line = 2 or j.fax_in_line = 3) then convert(nvarchar, j.fax_in_channel) + '(' + (case when j.fax_in_line = 1 then N'内線' when j.fax_in_line = 2 then N'外線' when j.fax_in_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_in_channel is not null then (convert(nvarchar, j.fax_in_channel)) else N'不明' end ) when ISNULL(j.job_detail_type,0) IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_out_line = 1 or j.fax_out_line = 2 or j.fax_out_line = 3) then convert(nvarchar, j.fax_out_channel) + '(' + (case when j.fax_out_line = 1 then N'内線' when j.fax_out_line = 2 then N'外線' when j.fax_out_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_out_channel is not null then (convert(nvarchar, j.fax_out_channel)) else N'不明' end ) else N'不明' end ) AS faxLineChannel, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_indication_type,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_indication_type,0)  ELSE j.fax_out_remote_indication_type END  AS faxRemoteIndicationType, "
			+ "    CASE WHEN j.fold_type = 6 THEN jfc.type ELSE j.fold_type END AS foldType, "
			+ "    null AS rollSize, "
			+ "    j.fax_out_status AS faxOutStatus, "
			+ "    null AS faxTransmissionRate, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_start_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_start_time  ELSE j.fax_out_start_time END  AS faxStartTime, "
			+ "    null AS rollLength, "
			+ "    j.fax_speed_number AS faxShortDialInNumber, "
			+ "    j.fax_dial_in_number AS faxDialInNumber, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_phone_number,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_phone_number,'')  ELSE j.fax_out_phone_number END  AS faxPhoneNumber, "
			+ "    j.job_type AS jobType, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (317,605,606,614,615,701,703,704,705,706,622,707,315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612,301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.protocol,0) WHEN ISNULL(job_detail_type,0) IN (317,605,606,614,615,701,703,704,705,706,622,707) THEN ISNULL(j.destination_protocol,0) WHEN ISNULL(job_detail_type,0) IN (315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612) THEN ISNULL(j.fax_in_protocol,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_protocol,0)  END  AS protocol, "
			+ "    j.host_name AS hostName, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_complete_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_complete_time  ELSE j.fax_out_complete_time END  AS faxCompleteTime, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_result,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_result,0)  ELSE j.fax_out_result END  AS faxResult, "
			+ "    j.jobID AS jobID, "
			+ "    j.fault_code AS jobFaultCode, "
			+ "    j.serialNumber AS serialNumber, "
			+ "    jdi.color_mode AS inputColorMode, "
			+ "    j.fax_resend AS faxReconnectFlag, "
			+ "    j.complete_time AS completeTime, "
			+ "    j.card_id AS cardID, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_id,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_id,'')  ELSE j.fax_out_remote_id END  AS faxRemoteID, "
			+ "    j.job_client_id AS jobClientID, "
			+ "    j.uuid AS uuid, "
			+ "    j.document_name AS documentName, "
			+ "    null AS outputColorMode, "
			+ "    jdi.size AS inputPaperSize, "
			+ "    null AS faxSize, "
			+ "    null AS impressions, "
			+ "    j.modelCode AS modelCode, "
			+ "    null AS outputPaperSize, "
			+ "    CASE WHEN ISNULL(j.document_format,0) <> 0 THEN j.document_format WHEN ISNULL(j.destination_document_format,0) <> 0 THEN j.destination_document_format  ELSE j.destination_document_format END  AS documentFormat, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_call,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_call,0)  ELSE j.fax_out_call END  AS faxCall, "
			+ "    j.job_template AS jobTemplate, "
			+ "    j.job_detail_type AS jobDetailType, "
			+ "    j.staple_type AS stapleType, "
			+ "    jdi.type AS inputPaperType, "
			+ "    j.user_id AS jobOwnerName "
			+ "FROM "
			+ "    jobLogInfo j LEFT JOIN jobLogFoldCounterInfo jfc ON j.autoNumber = jfc.jobLogID, "
			+ "    jobLogDetailInputInfo jdi, "
			+ "    DMDeviceTempInfo d "
			+ "WHERE "
			+ "    (j.autoNumber = jdi.jobLogID) AND "
			+ "    (j.modelCode = d.DeviceModelCode) AND "
			+ "    (j.serialNumber = d.DeviceSerialNumber) AND "
			+ "    ( (1=1)  AND  (j.complete_time < '2013/7/20 0:0') ) ";
	
	private static final String S3 =
	" SELECT TOP 1000 "
	+ "    j.punch_type AS punchType, "
	+ "    CASE WHEN j.fold_type = 6 THEN jfc.value ELSE j.fold_count END AS foldCount, "
	+ "    j.bind_count AS bindCount, "
	+ "    jdo.type AS outputPaperType, "
	+ "    null AS relatedJobID, "
	+ "    j.fax_sender_number AS faxSenderNumber, "
	+ "    null AS faxTotalPages, "
	+ "    j.mailbox_document_id AS mailboxDocumentID, "
	+ "    j.mailbox_number AS mailboxNumber, "
	+ "    j.staple_count AS stapleCount, "
	+ "    j.fax_address_book_name AS faxAddressBookName, "
	+ "    j.copies AS copies, "
	+ "    j.punch_count AS punchCount, "
	+ "    j.destination_address AS destinationAddress, "
	+ "    jdo.n_up AS nUp, "
	+ "    j.fault_reason AS jobFaultReason, "
	+ "    j.user_name AS userName, "
	+ "    d.DeviceModelName AS modelName, "
	+ "    j.destination AS destination, "
	+ "    j.host_address AS ipAddress, "
	+ "    null AS inputImpressions, "
	+ "    CASE WHEN jdo.input_tray IS NOT NULL THEN convert(nvarchar, jdo.input_tray) ELSE '' END + '-' + CASE WHEN jdo.input_tray_no IS NOT NULL THEN convert(nvarchar, jdo.input_tray_no) ELSE '' END AS inputTray, "
	+ "    null AS faxPages, "
	+ "    j.account_id AS accountID, "
	+ "    j.start_time AS startTime, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_in,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_out,'')  ELSE j.f_code_out END  AS fCode, "
	+ "    j.collection_time AS collectionTime, "
	+ "    CASE WHEN jdo.output_tray IS NOT NULL THEN convert(nvarchar, jdo.output_tray) ELSE '' END + '-' + CASE WHEN jdo.output_tray_no IS NOT NULL THEN convert(nvarchar, jdo.output_tray_no) ELSE '' END AS outputTray, "
	+ "    CASE WHEN j.fold_type = 6 THEN 1 ELSE 0 END AS foldCounterFlg, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_duration,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_duration,0)  ELSE j.fax_out_duration END  AS faxDuration, "
	+ "    d.DeviceDisplayName AS printerName, "
	+ "    jds.type AS stampType, "
	+ "    jdo.sheets AS sheets, "
	+ "    j.job_status AS jobStatus, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_name,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_name,'')  ELSE j.fax_out_remote_name END  AS faxRemoteName, "
	+ "    (case when ISNULL(j.job_detail_type,0) NOT IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_in_line = 1 or j.fax_in_line = 2 or j.fax_in_line = 3) then convert(nvarchar, j.fax_in_channel) + '(' + (case when j.fax_in_line = 1 then N'内線' when j.fax_in_line = 2 then N'外線' when j.fax_in_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_in_channel is not null then (convert(nvarchar, j.fax_in_channel)) else N'不明' end ) when ISNULL(j.job_detail_type,0) IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_out_line = 1 or j.fax_out_line = 2 or j.fax_out_line = 3) then convert(nvarchar, j.fax_out_channel) + '(' + (case when j.fax_out_line = 1 then N'内線' when j.fax_out_line = 2 then N'外線' when j.fax_out_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_out_channel is not null then (convert(nvarchar, j.fax_out_channel)) else N'不明' end ) else N'不明' end ) AS faxLineChannel, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_indication_type,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_indication_type,0)  ELSE j.fax_out_remote_indication_type END  AS faxRemoteIndicationType, "
	+ "    CASE WHEN j.fold_type = 6 THEN jfc.type ELSE j.fold_type END AS foldType, "
	+ "    jdo.standard_width AS rollSize, "
	+ "    j.fax_out_status AS faxOutStatus, "
	+ "    null AS faxTransmissionRate, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_start_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_start_time  ELSE j.fax_out_start_time END  AS faxStartTime, "
	+ "    jdo.length AS rollLength, "
	+ "    j.fax_speed_number AS faxShortDialInNumber, "
	+ "    j.fax_dial_in_number AS faxDialInNumber, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_phone_number,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_phone_number,'')  ELSE j.fax_out_phone_number END  AS faxPhoneNumber, "
	+ "    j.job_type AS jobType, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (317,605,606,614,615,701,703,704,705,706,622,707,315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612,301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.protocol,0) WHEN ISNULL(job_detail_type,0) IN (317,605,606,614,615,701,703,704,705,706,622,707) THEN ISNULL(j.destination_protocol,0) WHEN ISNULL(job_detail_type,0) IN (315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612) THEN ISNULL(j.fax_in_protocol,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_protocol,0)  END  AS protocol, "
	+ "    j.host_name AS hostName, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_complete_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_complete_time  ELSE j.fax_out_complete_time END  AS faxCompleteTime, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_result,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_result,0)  ELSE j.fax_out_result END  AS faxResult, "
	+ "    j.jobID AS jobID, "
	+ "    j.fault_code AS jobFaultCode, "
	+ "    j.serialNumber AS serialNumber, "
	+ "    null AS inputColorMode, "
	+ "    j.fax_resend AS faxReconnectFlag, "
	+ "    j.complete_time AS completeTime, "
	+ "    j.card_id AS cardID, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_id,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_id,'')  ELSE j.fax_out_remote_id END  AS faxRemoteID, "
	+ "    j.job_client_id AS jobClientID, "
	+ "    j.uuid AS uuid, "
	+ "    j.document_name AS documentName, "
	+ "    jdo.color_mode AS outputColorMode, "
	+ "    null AS inputPaperSize, "
	+ "    null AS faxSize, "
	+ "    jdo.impressions AS impressions, "
	+ "    j.modelCode AS modelCode, "
	+ "    jdo.size AS outputPaperSize, "
	+ "    CASE WHEN ISNULL(j.document_format,0) <> 0 THEN j.document_format WHEN ISNULL(j.destination_document_format,0) <> 0 THEN j.destination_document_format  ELSE j.destination_document_format END  AS documentFormat, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_call,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_call,0)  ELSE j.fax_out_call END  AS faxCall, "
	+ "    j.job_template AS jobTemplate, "
	+ "    j.job_detail_type AS jobDetailType, "
	+ "    j.staple_type AS stapleType, "
	+ "    null AS inputPaperType, "
	+ "    j.user_id AS jobOwnerName "
	+ "FROM "
	+ "    jobLogInfo j LEFT JOIN jobLogFoldCounterInfo jfc ON j.autoNumber = jfc.jobLogID LEFT JOIN jobLogStampCounterInfo jds ON j.autoNumber = jds.jobLogID, "
	+ "    jobLogDetailOutputInfo jdo, "
	+ "    DMDeviceTempInfo d "
	+ "WHERE "
	+ "    (j.autoNumber = jdo.jobLogID) AND "
	+ "    (j.modelCode = d.DeviceModelCode) AND "
	+ "    (j.serialNumber = d.DeviceSerialNumber) AND "
	+ "    ( (1=1)  AND  (j.complete_time < '2013/7/20 0:0') ) ";
	private static final String S4 =
	" SELECT TOP 1000 "
	+ "    j.punch_type AS punchType, "
	+ "    CASE WHEN j.fold_type = 6 THEN jfc.value ELSE j.fold_count END AS foldCount, "
	+ "    j.bind_count AS bindCount, "
	+ "    null AS outputPaperType, "
	+ "    null AS relatedJobID, "
	+ "    j.fax_sender_number AS faxSenderNumber, "
	+ "    null AS faxTotalPages, "
	+ "    j.mailbox_document_id AS mailboxDocumentID, "
	+ "    j.mailbox_number AS mailboxNumber, "
	+ "    j.staple_count AS stapleCount, "
	+ "    j.fax_address_book_name AS faxAddressBookName, "
	+ "    j.copies AS copies, "
	+ "    j.punch_count AS punchCount, "
	+ "    j.destination_address AS destinationAddress, "
	+ "    null AS nUp, "
	+ "    j.fault_reason AS jobFaultReason, "
	+ "    j.user_name AS userName, "
	+ "    d.DeviceModelName AS modelName, "
	+ "    j.destination AS destination, "
	+ "    j.host_address AS ipAddress, "
	+ "    jds.impressions AS inputImpressions, "
	+ "    null AS inputTray, "
	+ "    null AS faxPages, "
	+ "    j.account_id AS accountID, "
	+ "    j.start_time AS startTime, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_in,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_out,'')  ELSE j.f_code_out END  AS fCode, "
	+ "    j.collection_time AS collectionTime, "
	+ "    null AS outputTray, "
	+ "    CASE WHEN j.fold_type = 6 THEN 1 ELSE 0 END AS foldCounterFlg, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_duration,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_duration,0)  ELSE j.fax_out_duration END  AS faxDuration, "
	+ "    d.DeviceDisplayName AS printerName, "
	+ "    null AS stampType, "
	+ "    null AS sheets, "
	+ "    j.job_status AS jobStatus, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_name,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_name,'')  ELSE j.fax_out_remote_name END  AS faxRemoteName, "
	+ "    (case when ISNULL(j.job_detail_type,0) NOT IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_in_line = 1 or j.fax_in_line = 2 or j.fax_in_line = 3) then convert(nvarchar, j.fax_in_channel) + '(' + (case when j.fax_in_line = 1 then N'内線' when j.fax_in_line = 2 then N'外線' when j.fax_in_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_in_channel is not null then (convert(nvarchar, j.fax_in_channel)) else N'不明' end ) when ISNULL(j.job_detail_type,0) IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_out_line = 1 or j.fax_out_line = 2 or j.fax_out_line = 3) then convert(nvarchar, j.fax_out_channel) + '(' + (case when j.fax_out_line = 1 then N'内線' when j.fax_out_line = 2 then N'外線' when j.fax_out_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_out_channel is not null then (convert(nvarchar, j.fax_out_channel)) else N'不明' end ) else N'不明' end ) AS faxLineChannel, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_indication_type,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_indication_type,0)  ELSE j.fax_out_remote_indication_type END  AS faxRemoteIndicationType, "
	+ "    CASE WHEN j.fold_type = 6 THEN jfc.type ELSE j.fold_type END AS foldType, "
	+ "    null AS rollSize, "
	+ "    j.fax_out_status AS faxOutStatus, "
	+ "    null AS faxTransmissionRate, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_start_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_start_time  ELSE j.fax_out_start_time END  AS faxStartTime, "
	+ "    null AS rollLength, "
	+ "    j.fax_speed_number AS faxShortDialInNumber, "
	+ "    j.fax_dial_in_number AS faxDialInNumber, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_phone_number,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_phone_number,'')  ELSE j.fax_out_phone_number END  AS faxPhoneNumber, "
	+ "    j.job_type AS jobType, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (317,605,606,614,615,701,703,704,705,706,622,707,315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612,301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.protocol,0) WHEN ISNULL(job_detail_type,0) IN (317,605,606,614,615,701,703,704,705,706,622,707) THEN ISNULL(j.destination_protocol,0) WHEN ISNULL(job_detail_type,0) IN (315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612) THEN ISNULL(j.fax_in_protocol,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_protocol,0)  END  AS protocol, "
	+ "    j.host_name AS hostName, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_complete_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_complete_time  ELSE j.fax_out_complete_time END  AS faxCompleteTime, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_result,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_result,0)  ELSE j.fax_out_result END  AS faxResult, "
	+ "    j.jobID AS jobID, "
	+ "    j.fault_code AS jobFaultCode, "
	+ "    j.serialNumber AS serialNumber, "
	+ "    jds.color_mode AS inputColorMode, "
	+ "    j.fax_resend AS faxReconnectFlag, "
	+ "    j.complete_time AS completeTime, "
	+ "    j.card_id AS cardID, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_id,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_id,'')  ELSE j.fax_out_remote_id END  AS faxRemoteID, "
	+ "    j.job_client_id AS jobClientID, "
	+ "    j.uuid AS uuid, "
	+ "    j.document_name AS documentName, "
	+ "    null AS outputColorMode, "
	+ "    null AS inputPaperSize, "
	+ "    null AS faxSize, "
	+ "    null AS impressions, "
	+ "    j.modelCode AS modelCode, "
	+ "    null AS outputPaperSize, "
	+ "    CASE WHEN ISNULL(j.document_format,0) <> 0 THEN j.document_format WHEN ISNULL(j.destination_document_format,0) <> 0 THEN j.destination_document_format  ELSE j.destination_document_format END  AS documentFormat, "
	+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_call,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_call,0)  ELSE j.fax_out_call END  AS faxCall, "
	+ "    j.job_template AS jobTemplate, "
	+ "    j.job_detail_type AS jobDetailType, "
	+ "    j.staple_type AS stapleType, "
	+ "    null AS inputPaperType, "
	+ "    j.user_id AS jobOwnerName "
	+ "FROM "
	+ "    jobLogInfo j LEFT JOIN jobLogFoldCounterInfo jfc ON j.autoNumber = jfc.jobLogID, "
	+ "    jobLogDetailSendInfo jds, "
	+ "    DMDeviceTempInfo d "
	+ "WHERE "
	+ "    (j.autoNumber = jds.jobLogID) AND "
	+ "    (j.modelCode = d.DeviceModelCode) AND "
	+ "    (j.serialNumber = d.DeviceSerialNumber) AND "
	+ "    ( (1=1)  AND  (j.complete_time < '2013/7/20 0:0') ) ";
	
	private static final String S5 = ""
			+ " SELECT TOP 1000 "
			+ "    j.punch_type AS punchType, "
			+ "    CASE WHEN j.fold_type = 6 THEN jfc.value ELSE j.fold_count END AS foldCount, "
			+ "    j.bind_count AS bindCount, "
			+ "    null AS outputPaperType, "
			+ "    null AS relatedJobID, "
			+ "    j.fax_sender_number AS faxSenderNumber, "
			+ "    null AS faxTotalPages, "
			+ "    j.mailbox_document_id AS mailboxDocumentID, "
			+ "    j.mailbox_number AS mailboxNumber, "
			+ "    j.staple_count AS stapleCount, "
			+ "    j.fax_address_book_name AS faxAddressBookName, "
			+ "    j.copies AS copies, "
			+ "    j.punch_count AS punchCount, "
			+ "    j.destination_address AS destinationAddress, "
			+ "    null AS nUp, "
			+ "    j.fault_reason AS jobFaultReason, "
			+ "    j.user_name AS userName, "
			+ "    d.DeviceModelName AS modelName, "
			+ "    j.destination AS destination, "
			+ "    j.host_address AS ipAddress, "
			+ "    null AS inputImpressions, "
			+ "    null AS inputTray, "
			+ "    null AS faxPages, "
			+ "    j.account_id AS accountID, "
			+ "    j.start_time AS startTime, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_in,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.f_code_out,'')  ELSE j.f_code_out END  AS fCode, "
			+ "    j.collection_time AS collectionTime, "
			+ "    null AS outputTray, "
			+ "    CASE WHEN j.fold_type = 6 THEN 1 ELSE 0 END AS foldCounterFlg, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_duration,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_duration,0)  ELSE j.fax_out_duration END  AS faxDuration, "
			+ "    d.DeviceDisplayName AS printerName, "
			+ "    null AS stampType, "
			+ "    null AS sheets, "
			+ "    j.job_status AS jobStatus, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_name,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_name,'')  ELSE j.fax_out_remote_name END  AS faxRemoteName, "
			+ "    (case when ISNULL(j.job_detail_type,0) NOT IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_in_line = 1 or j.fax_in_line = 2 or j.fax_in_line = 3) then convert(nvarchar, j.fax_in_channel) + '(' + (case when j.fax_in_line = 1 then N'内線' when j.fax_in_line = 2 then N'外線' when j.fax_in_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_in_channel is not null then (convert(nvarchar, j.fax_in_channel)) else N'不明' end ) when ISNULL(j.job_detail_type,0) IN ( 301,303,304,305,306,307,308,309,310,311,312,313,314) then (case when (j.fax_out_line = 1 or j.fax_out_line = 2 or j.fax_out_line = 3) then convert(nvarchar, j.fax_out_channel) + '(' + (case when j.fax_out_line = 1 then N'内線' when j.fax_out_line = 2 then N'外線' when j.fax_out_line = 3 then N'IPファクス(SIP)' else '' end) + ')' when j.fax_out_channel is not null then (convert(nvarchar, j.fax_out_channel)) else N'不明' end ) else N'不明' end ) AS faxLineChannel, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_indication_type,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_indication_type,0)  ELSE j.fax_out_remote_indication_type END  AS faxRemoteIndicationType, "
			+ "    CASE WHEN j.fold_type = 6 THEN jfc.type ELSE j.fold_type END AS foldType, "
			+ "    null AS rollSize, "
			+ "    j.fax_out_status AS faxOutStatus, "
			+ "    null AS faxTransmissionRate, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_start_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_start_time  ELSE j.fax_out_start_time END  AS faxStartTime, "
			+ "    null AS rollLength, "
			+ "    j.fax_speed_number AS faxShortDialInNumber, "
			+ "    j.fax_dial_in_number AS faxDialInNumber, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_phone_number,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_phone_number,'')  ELSE j.fax_out_phone_number END  AS faxPhoneNumber, "
			+ "    j.job_type AS jobType, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (317,605,606,614,615,701,703,704,705,706,622,707,315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612,301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.protocol,0) WHEN ISNULL(job_detail_type,0) IN (317,605,606,614,615,701,703,704,705,706,622,707) THEN ISNULL(j.destination_protocol,0) WHEN ISNULL(job_detail_type,0) IN (315,418,419,420,421,422,423,424,425,426,427,607,608,609,610,611,612) THEN ISNULL(j.fax_in_protocol,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_protocol,0)  END  AS protocol, "
			+ "    j.host_name AS hostName, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_in_complete_time WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN j.fax_out_complete_time  ELSE j.fax_out_complete_time END  AS faxCompleteTime, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_result,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_result,0)  ELSE j.fax_out_result END  AS faxResult, "
			+ "    j.jobID AS jobID, "
			+ "    j.fault_code AS jobFaultCode, "
			+ "    j.serialNumber AS serialNumber, "
			+ "    null AS inputColorMode, "
			+ "    j.fax_resend AS faxReconnectFlag, "
			+ "    j.complete_time AS completeTime, "
			+ "    j.card_id AS cardID, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_remote_id,'') WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_remote_id,'')  ELSE j.fax_out_remote_id END  AS faxRemoteID, "
			+ "    j.job_client_id AS jobClientID, "
			+ "    j.uuid AS uuid, "
			+ "    j.document_name AS documentName, "
			+ "    null AS outputColorMode, "
			+ "    null AS inputPaperSize, "
			+ "    null AS faxSize, "
			+ "    null AS impressions, "
			+ "    j.modelCode AS modelCode, "
			+ "    null AS outputPaperSize, "
			+ "    CASE WHEN ISNULL(j.document_format,0) <> 0 THEN j.document_format WHEN ISNULL(j.destination_document_format,0) <> 0 THEN j.destination_document_format  ELSE j.destination_document_format END  AS documentFormat, "
			+ "    CASE WHEN ISNULL(job_detail_type,0) NOT IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_in_call,0) WHEN ISNULL(job_detail_type,0) IN (301,303,304,305,306,307,308,309,310,311,312,313,314) THEN ISNULL(j.fax_out_call,0)  ELSE j.fax_out_call END  AS faxCall, "
			+ "    j.job_template AS jobTemplate, "
			+ "    j.job_detail_type AS jobDetailType, "
			+ "    j.staple_type AS stapleType, "
			+ "    null AS inputPaperType, "
			+ "    j.user_id AS jobOwnerName "
			+ "FROM "
			+ "    jobLogInfo j LEFT JOIN jobLogFoldCounterInfo jfc ON j.autoNumber = jfc.jobLogID, "
			+ "    DMDeviceTempInfo d "
			+ "WHERE "
			+ "    (j.modelCode = d.DeviceModelCode) AND "
			+ "    (j.serialNumber = d.DeviceSerialNumber) AND "
			+ "    (j.autoNumber NOT IN (SELECT DISTINCT jobLogID FROM jobLogDetailFaxInfo UNION SELECT DISTINCT jobLogID FROM jobLogDetailInputInfo UNION SELECT DISTINCT jobLogID FROM jobLogDetailOutputInfo UNION SELECT DISTINCT jobLogID FROM jobLogDetailSendInfo)) AND "
			+ "    ( (1=1)  AND  (j.complete_time < '2013/7/20 0:0') ) ";
	
	private static final String SQL = " SELECT "
			+ "        jobFaultReason, "
			+ "        fCode, "
			+ "        startTime, "
			+ "        modelCode, "
			+ "        serialNumber, "
			+ "        modelName, "
			+ "        faxRemoteIndicationType, "
			+ "        inputPaperSize, "
			+ "        stapleType, "
			+ "        mailboxNumber, "
			+ "        uuid, "
			+ "        hostName, "
			+ "        faxSenderNumber, "
			+ "        jobStatus, "
			+ "        punchCount, "
			+ "        faxPhoneNumber, "
			+ "        faxResult, "
			+ "        cardID, "
			+ "        faxAddressBookName, "
			+ "        outputTray, "
			+ "        jobOwnerName, "
			+ "        ipAddress, "
			+ "        mailboxDocumentID, "
			+ "        destination, "
			+ "        printerName, "
			+ "        protocol, "
			+ "        nUp, "
			+ "        faxDialInNumber, "
			+ "        jobClientID, "
			+ "        stapleCount, "
			+ "        copies, "
			+ "        faxCall, "
			+ "        impressions, "
			+ "        faxRemoteName, "
			+ "        bindCount, "
			+ "        faxOutStatus, "
			+ "        faxLineChannel, "
			+ "        foldType, "
			+ "        foldCounterFlg, "
			+ "        userName, "
			+ "        faxSize, "
			+ "        faxTransmissionRate, "
			+ "        sheets, "
			+ "        foldCount, "
			+ "        jobFaultCode, "
			+ "        jobType, "
			+ "        inputTray, "
			+ "        faxReconnectFlag, "
			+ "        punchType, "
			+ "        relatedJobID, "
			+ "        jobID, "
			+ "        faxStartTime, "
			+ "        outputColorMode, "
			+ "        documentName, "
			+ "        jobTemplate, "
			+ "        collectionTime, "
			+ "        faxRemoteID, "
			+ "        completeTime, "
			+ "        faxDuration, "
			+ "        documentFormat, "
			+ "        accountID, "
			+ "        faxTotalPages, "
			+ "        outputPaperType, "
			+ "        faxCompleteTime, "
			+ "        inputImpressions, "
			+ "        jobDetailType, "
			+ "        inputColorMode, "
			+ "        destinationAddress, "
			+ "        inputPaperType, "
			+ "        outputPaperSize, "
			+ "        faxPages, "
			+ "        faxShortDialInNumber, "
			+ "        stampType, "
			+ "        rollSize, "
			+ "        rollLength , "
			+ "        row_number() OVER (ORDER BY hostName asc) AS rownum "
			+ "    FROM "
			+ " ("
			+ S1
			+ " UNION  "
			+ S2
			+ " UNION  "
			+ S3
			+ " UNION  "
			+ S4
			+ " UNION  "
			+ S5
			+ "        ) A "
			+ " ORDER BY startTime ";
	
	public static List<SortBean> getSortBean(Connection con, String sql) throws Exception {
		
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		List<SortBean> sbs = new ArrayList<SortBean>();
		while (rs.next()) {
			Map<String, Object> r = new HashMap<String, Object>();
			int cc = rs.getMetaData().getColumnCount();
			for (int i = 0; i < cc; i++) {
				r.put(rs.getMetaData().getColumnLabel(i+1), rs.getObject(i+1));
			}
			sbs.add(new SortBean(r));
		}
		rs.close();
		ps.close();
		return sbs;
	}

	
	private static final String SQL2 = "SELECT * FROM jobLogDetailInputInfo WHERE joblogID IN (";
	private static final int PC = 10000;
	
	public static void paramCountTest(Connection con) throws Exception {
		StringBuffer sb = new StringBuffer(SQL2);
//		List<Integer> params = new ArrayList<Integer>();
		
		for (int i = 0; i < PC; i++) {
			sb.append(" " + (i+9999999999999999L) + ",");
//			params.add(i+10000000);
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(")");
//		System.out.println(params.size());
		System.out.println(sb.toString());
		PreparedStatement ps = con.prepareStatement(sb.toString());
//		for (int i = 0; i < params.size(); i++) {
//			ps.setInt(i+1, params.get(i));
//		}
		ResultSet rs = ps.executeQuery();
		rs.close();
		ps.close();
	}
	

	public static void main(String[] args) throws Exception {
//		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//		SQLServerDataSource ds = new SQLServerDataSource();
//		ds.setDatabaseName("BM_RMGT_DATA");
//		ds.setServerName("localhost");
//		ds.setPortNumber(1433); 
//		ds.setUser("sa");
//		ds.setPassword("overseas");
//		ds.setInstanceName("FXAW");
//		Connection con = ds.getConnection();
//		
//		paramCountTest(con);

//		List<SortBean> sbs = new ArrayList<SortBean>();
//		long start = System.currentTimeMillis();
//		sbs.addAll(getSortBean(con, SQL));
//		long end = System.currentTimeMillis();
//
//		System.out.println("start = " + start + "  end = " + end + " end - start = " + (end-start));
//		System.out.println(sbs.size());
//
//		sbs = new ArrayList<SortBean>();
//		long start1 = System.currentTimeMillis();
//		sbs.addAll(getSortBean(con, S1));
//		sbs.addAll(getSortBean(con, S2));
//		sbs.addAll(getSortBean(con, S3));
//		sbs.addAll(getSortBean(con, S4));
//		sbs.addAll(getSortBean(con, S5));
//		Collections.sort(sbs, new SortCompare("startTime"));
//		long end1 = System.currentTimeMillis();
//
//		System.out.println("start = " + start1 + "  end = " + end1+ " end - start = " + (end1-start1));
//		System.out.println(sbs.size());
//		con.close();
	}
}
